package com.moorror.codegenvel.db.sqlite;

import com.google.common.collect.Sets;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;

import java.io.IOException;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 需要两个依赖jar
 * commons-dbcp.jar
 * commons-pool.jar
 *
 */
public class DatabaseBasicService {
    private  static final String DATABASE_DRIVER = "org.sqlite.JDBC";
    private  static final String DATABASE_URL = "jdbc:sqlite:" + SqliteConstance.DB_FILE_PATH;
    private static final String EDITOR_OFFSET_START = "offset_start";
    private static final String EDITOR_OFFSET_END = "offset_end";
    private static final String IMAGE_RECORDS = "image_records";
    private BasicDataSource source;

    public DatabaseBasicService() {
        try {
            //创建了DBCP的数据库连接池
            source = setupDataSource(DATABASE_URL, DATABASE_DRIVER);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 如果不存在,创建DB文件
        createFileAndDir();
        // 如果表不存在,创建表
        initTable();
    }


    public  BasicDataSource getSource() {
        return source;
    }

    public  Connection getConnection() throws SQLException {
        return source.getConnection();
    }

    /**
     * 释放资源
     */
    public  void closeResource(Connection conn, Statement statement, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 如果不存在目录和文件就创建
     */
    private  void createFileAndDir() {
        //"C:\Users\Administrator\.ideaNotebooksFile"
        if (!Files.exists(SqliteConstance.PROJECT_DB_DIRECTORY_PATH)) {
            try {
                Files.createDirectories(SqliteConstance.PROJECT_DB_DIRECTORY_PATH);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //"C:\Users\Administrator\.ideaNotebooksFile\notebooks.db"
        if (!Files.exists(SqliteConstance.DB_FILE_PATH)) {
            try {
                Files.createFile(SqliteConstance.DB_FILE_PATH);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //创建图片文件夹.
        if (!Files.exists(SqliteConstance.IMAGE_DIRECTORY_PATH)) {
            try {
                Files.createDirectories(SqliteConstance.IMAGE_DIRECTORY_PATH);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        //创建图片临时文件夹.
        if (!Files.exists(SqliteConstance.TEMP_IMAGE_DIRECTORY_PATH)) {
            try {
                Files.createDirectories(SqliteConstance.TEMP_IMAGE_DIRECTORY_PATH);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 如果不存在就初始化表
     */
    private  void initTable() {
        /**
         * CodeGen -- scene
         */

        String createCodeGenSceneSQL = "CREATE TABLE IF NOT EXISTS codegen_scene (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT unique," +
                "desc_scene TEXT," +
                "json_context TEXT," +
                "sql_ddl TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")";

        String createCodeGenSceneTemplateSQL = "CREATE TABLE IF NOT EXISTS codegen_scenetemplate (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "scene TEXT," +
                "template TEXT," +
                "json_scene_template TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER," +
                "UNIQUE(scene, template)" +
                ")";

        String createCodeGenTemplateSQL = "CREATE TABLE IF NOT EXISTS codegen_template (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT unique," +
                "desc_template TEXT," +
                "json_context TEXT," +
                "content TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")";

        /**
         * CodeGen -- template
         */

        /**
         * CodeGenXRender
         */

        String createCodegenXRenderDbConnectionSQL = "CREATE TABLE IF NOT EXISTS codegen_xrender_db_connection (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT unique," +
                "url TEXT," +
                "username TEXT," +
                "password TEXT," +
                "other_config_json TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")";

        String createCodegenXRenderTableSQL = "CREATE TABLE IF NOT EXISTS codegen_xrender_table (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "kf TEXT," +
                "name_table TEXT," +
                "name_table_java TEXT," +
                "ext_column_json TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER," +
                "UNIQUE(kf, name_table, name_table_java)" +
                ")";

        String createCodegenXRenderTableConfigSQL = "CREATE TABLE IF NOT EXISTS codegen_xrender_table_config (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "kf TEXT," +
                "name_table_scene TEXT," +
                "json_table_config TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER," +
                "UNIQUE(kf, name_table_scene)" +
                ")";

/**
 * CodeGenXRender
 */


        /**
         * History
         */

        String createHistoryTableSQL = "CREATE TABLE IF NOT EXISTS common_history_table (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "kf TEXT," +
                "scene TEXT," +
                "ext_column_json TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER," +
                "UNIQUE(kf, scene)" +
                ")";
        /**
         * History
         */

        try {
            QueryRunner queryRunner = new QueryRunner(getSource());
            queryRunner.update(createCodeGenSceneSQL);
            queryRunner.update(createCodeGenSceneTemplateSQL);
            queryRunner.update(createCodeGenTemplateSQL);

            queryRunner.update(createCodegenXRenderDbConnectionSQL);
            queryRunner.update(createCodegenXRenderTableSQL);
            queryRunner.update(createCodegenXRenderTableConfigSQL);
            queryRunner.update(createHistoryTableSQL);
            updateAddColumn(queryRunner);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 判断是否存在某个字段的方法
     * @param table 表
     * @param column 字段/列
     * @param queryRunner 查询
     * @return true 存在该字段 false 不存在该字段
     * @throws SQLException sql异常
     */
    private boolean isColumnExists (String table, String column,QueryRunner queryRunner ) throws SQLException {
        boolean isExists = false;
        List<String> name = queryRunner.query("PRAGMA table_info("+table+")", new ColumnListHandler<String>("name"));
        for (String s : name) {
            if (column.equalsIgnoreCase(s)) {
                isExists = true;
                break;
            }
        }
        return isExists;
    }

    public void updateAddColumn(QueryRunner queryRunner) throws SQLException {
        Map<String, List<SqliteUpdateEnum>> tableNameUpdataMap = SqliteUpdateEnum.tableNameUpdataMap();
        for (Map.Entry<String, List<SqliteUpdateEnum>> entry : tableNameUpdataMap.entrySet()) {
            List<String> name = queryRunner.query("PRAGMA table_info(" + entry.getKey() + ")", new ColumnListHandler<String>("name"));
            Set<String> columnNameSet = Sets.newTreeSet(name);
            for (SqliteUpdateEnum sqliteUpdateEnum : entry.getValue()) {
                if (columnNameSet.contains(sqliteUpdateEnum.getColumnName())) {
                    continue;
                }
                queryRunner.update(sqliteUpdateEnum.getAddColumnSql());
            }
        }
    }

    public static BasicDataSource setupDataSource(String connectURI, String driverClass){
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(driverClass);
        ds.setUrl(connectURI);
        return ds;
    }
}
